

ALTER          function fn_prevoius_lineitems(@salesorder_id int)

returns  @fn_result Table(CCO_Product_ID int,
			CCO_Line_Item_No int,
			CCO_Qty int,
                        CCO_Unit_Cost float,
                        CCO_Amount float,
                        CCO_Requested_Date datetime,
			CCO_Change_Number int,
                        CCOL_Product_ID int,
                        CCOL_Line_Item_No int,
			CCOL_Qty int,
                        CCOL_Unit_Cost float,
                        CCOL_Amount float,
                        CCOL_Requested_Date datetime,
			CCOL_Change_Number int,
                        CSOL_Product_ID int,
                        CSOL_Line_Item_No int,
			CSOL_Qty int,
                        CSOL_Unit_Cost float,
                        CSOL_Amount float,
                        CSOL_Requested_Date datetime)
			


as
begin
	declare @cco_product_id int
        declare @cco_line_item_no int
	declare @cco_qty int
	declare @cco_unit_cost float
	declare @cco_amount float
        declare @cco_requested_date datetime
        declare @cco_change_number int
        declare @ccol_product_id int
        declare @ccol_line_item_no int
	declare @ccol_qty int
	declare @ccol_unit_cost float
	declare @ccol_amount float
        declare @ccol_requested_date datetime
        declare @ccol_change_number int
        declare @csol_product_id int
        declare @csol_line_item_no int
	declare @csol_qty int
	declare @csol_unit_cost float
	declare @csol_amount float
        declare @csol_requested_date datetime
        
	
         declare @simplecount int


	declare @change_orders_cursor CURSOR
	declare @cancel_orders_cursor CURSOR
        declare @shipped_orders_cursor CURSOR

	declare @result Table(CCO_Product_ID int,
			CCO_Line_Item_No int,
			CCO_Qty int,
                        CCO_Unit_Cost float,
                        CCO_Amount float,
                        CCO_Requested_Date datetime,
			CCO_Change_Number int,
                        CCOL_Product_ID int,
                        CCOL_Line_Item_No int,
			CCOL_Qty int,
                        CCOL_Unit_Cost float,
                        CCOL_Amount float,
                        CCOL_Requested_Date datetime,
			CCOL_Change_Number int,
                        CSOL_Product_ID int,
                        CSOL_Line_Item_No int,
			CSOL_Qty int,
                        CSOL_Unit_Cost float,
                        CSOL_Amount float,
                        CSOL_Requested_Date datetime)
			


SET @change_orders_cursor = CURSOR FOR 
    SELECT Product_ID ,CCO_Line_Item_No,CCO_Qty , CCO_Unit_Cost,CCO_Amount,CCO_Requested_Date,CCO_Change_Number
FROM Customer_Change_Order_LineItem WHERE SalesOrder_Id =  @salesorder_id

SET @cancel_orders_cursor = CURSOR FOR 
    SELECT Product_ID,CCOL_Line_Item_No,CCOL_Qty , CCOL_Unit_Cost,CCOL_Amount,CCOL_Requested_Date,CCOL_Change_Number
FROM Customer_Cancel_Order_LineItem WHERE SalesOrder_Id =  @salesorder_id


SET @shipped_orders_cursor = CURSOR FOR 
   SELECT Product_ID,CSOL_Line_Item_No,CSOL_Qty , CSOL_Unit_Cost,CSOL_Amount,CSOL_Requested_Date
FROM Customer_Shipped_Order_LineItem WHERE SalesOrder_Id =  @salesorder_id






open @change_orders_cursor		

	fetch next from  @change_orders_cursor into  @cco_product_id,@cco_line_item_no ,@cco_qty,@cco_unit_cost,
@cco_amount ,@cco_requested_date ,@cco_change_number

	while @@fetch_status = 0
	begin
		 insert  @result (CCO_Product_ID ,CCO_Line_Item_No,CCO_Qty , CCO_Unit_Cost,CCO_Amount,CCO_Requested_Date,CCO_Change_Number)
	         values (@cco_product_id ,@cco_line_item_no ,@cco_qty,@cco_unit_cost,
	         @cco_amount ,@cco_requested_date ,@cco_change_number)

	        fetch next from @change_orders_cursor into @cco_product_id,@cco_line_item_no ,@cco_qty,@cco_unit_cost,
                @cco_amount ,@cco_requested_date ,@cco_change_number
	  
	end 
close @change_orders_cursor
deallocate @change_orders_cursor


open @cancel_orders_cursor	

	fetch next from  @cancel_orders_cursor into @ccol_product_id,@ccol_line_item_no,@ccol_qty,
        @ccol_unit_cost,@ccol_amount,@ccol_requested_date,@ccol_change_number 

	while @@fetch_status = 0
	begin
		 insert  @result (CCOL_Product_ID,CCOL_Line_Item_No,CCOL_Qty , CCOL_Unit_Cost,CCOL_Amount,CCOL_Requested_Date,CCOL_Change_Number)
	         values (@ccol_product_id,@ccol_line_item_no,@ccol_qty,
                 @ccol_unit_cost,@ccol_amount,@ccol_requested_date,@ccol_change_number )

	        fetch next from @cancel_orders_cursor into @ccol_product_id,@ccol_line_item_no,@ccol_qty,
                 @ccol_unit_cost,@ccol_amount,@ccol_requested_date,@ccol_change_number 
	  
	end 
close @cancel_orders_cursor
deallocate @cancel_orders_cursor

open @shipped_orders_cursor

	fetch next from  @shipped_orders_cursor into @csol_product_id,@csol_line_item_no,@csol_line_item_no,
        @csol_unit_cost,@csol_amount,@csol_requested_date
	while @@fetch_status = 0
	begin
		 insert  @result (CSOL_Product_ID,CSOL_Line_Item_No,CSOL_Qty , CSOL_Unit_Cost,CSOL_Amount,CSOL_Requested_Date)
	         values (@csol_product_id,@csol_line_item_no,@csol_line_item_no,
                @csol_unit_cost,@csol_amount,@csol_requested_date)

	        fetch next from @shipped_orders_cursor into @csol_product_id,@csol_line_item_no,@csol_line_item_no,
                @csol_unit_cost,@csol_amount,@csol_requested_date
	  
	end 
close @shipped_orders_cursor
deallocate @shipped_orders_cursor



	
	
	
    insert @fn_result select * from @result 

  return 

END





